import os
import sqlite3
import pyodbc

def sqlite_dm(sqlite_path, table_name, pri_key, key_content):
    sqlite_path = sqlite_path.replace("\\", "\\\\")
    conn_sqlite = sqlite3.connect(sqlite_path)
    cursors_sqlite = conn_sqlite.cursor()

    # 获取列名
    cursors_sqlite.execute(f"PRAGMA table_info({table_name});")
    columns_info = cursors_sqlite.fetchall()
    col_names = [col[1] for col in columns_info]  # 第二列是列名

    # 执行查询
    query = f'SELECT * FROM {table_name} WHERE {pri_key}="{key_content}";'
    cursors_sqlite.execute(query)
    rows = cursors_sqlite.fetchall()

    if not rows:
        print("No matching data found in SQLite.")
        return

    # 连接达梦数据库
    conn_dm = pyodbc.connect(
        "DRIVER={DM8 ODBC DRIVER};"
        "SERVER=10.201.158.169;"
        "PORT=5236;"
        "UID=SYSDBA;"
        "PWD=Aa2676615043;"
    )
    cursor_dm = conn_dm.cursor()

    for row in rows:
        key_index = col_names.index(pri_key)
        key_value = row[key_index]

        # 检查记录是否已存在
        cursor_dm.execute(f"SELECT COUNT(*) FROM {table_name} WHERE {pri_key} = ?", key_value)
        exists = cursor_dm.fetchone()[0] > 0

        if exists:
            # 构造 UPDATE 语句
            update_parts = [f"{col} = ?" for col in col_names if col != pri_key]
            update_sql = f"UPDATE {table_name} SET {', '.join(update_parts)} WHERE {pri_key} = ?"
            update_values = [row[col_names.index(col)] for col in col_names if col != pri_key]
            update_values.append(key_value)  # 最后加上 WHERE 的主键值
            try:
                cursor_dm.execute(update_sql, update_values)
                print(f"记录已更新: {key_value}")
            except Exception as e:
                print(f"更新失败: {e}")
        else:
            # 构造 INSERT 语句
            col_placeholders = ", ".join(["?" for _ in col_names])
            col_names_str = ", ".join(col_names)
            insert_sql = f"INSERT INTO {table_name} ({col_names_str}) VALUES ({col_placeholders})"
            try:
                cursor_dm.execute(insert_sql, row)
                print(f"记录已插入: {key_value}")
            except Exception as e:
                print(f"插入失败: {e}")

    conn_dm.commit()
    print("操作完成。")

    # 关闭连接
    cursor_dm.close()
    conn_dm.close()
    cursors_sqlite.close()
    conn_sqlite.close()


# 示例调用
sqlite_path = os.path.join(os.getcwd(), "test.db")
sqlite_dm(sqlite_path, "sync", "ID", "000001")
